<?php
namespace App\Services;

use Ixudra\Curl\Facades\Curl;
use App\Services\Store;
use App\Services\Goods;
use DB;


class Distribution{
	
	// 获取下级列表
	public function getLowerList($sId, $userCode){
		
		$sql = "SELECT du.uId, t1.*, al.alName
FROM distribution_users AS du
LEFT JOIN 
(
SELECT mu.muNickName AS userName, mu.muCode AS userCode, mu.alId, mu.mcId AS sId
FROM member_users AS mu
WHERE mu.mcId = ?
UNION
SELECT au.name AS userName1, au.auCode AS userCode1,au.alId, au.sId
FROM admin_users AS au
WHERE au.sId = ?
) AS t1 ON du.uId = t1.userCode
LEFT JOIN agency_level AS al ON al.alId = t1.alId
WHERE du.dcId = ? AND du.duParentUid = ?;";

		$list = DB::select($sql, [$sId, $sId, $sId, $userCode]);
		
		return $list;
	}
	
	// 获取下下级列表
	public function getUnderTheLowerList($sId, $userCode){
		
		$sql = "SELECT du.uId, du.duParentUid, t1.*, al.alName
FROM distribution_users AS du
LEFT JOIN 
(
SELECT mu.muNickName AS userName, mu.muCode AS userCode, mu.alId, mu.mcId AS sId
FROM member_users AS mu
WHERE mu.mcId = ?
UNION
SELECT au.name AS userName1, au.auCode AS userCode1,au.alId, au.sId
FROM admin_users AS au
WHERE au.sId = ?
) AS t1 ON du.uId = t1.userCode
LEFT JOIN agency_level AS al ON al.alId = t1.alId
WHERE du.dcId = ? AND du.duGrandUid = ?;";

		$list = DB::select($sql, [$sId, $sId, $sId, $userCode]);
		
		return $list;
	}
	
	// 获取下级身份人数
	public function getLowerLevelCount($sId, $userCode){
		
		$sql = "SELECT du.uId, t1.*, al.alName, COUNT(1) AS num
FROM distribution_users AS du
LEFT JOIN 
(
SELECT mu.muNickName AS userName, mu.muCode AS userCode, mu.alId, mu.mcId AS sId
FROM member_users AS mu
WHERE mu.mcId = ?
UNION
SELECT au.name AS userName1, au.auCode AS userCode1,au.alId, au.sId
FROM admin_users AS au
WHERE au.sId = ?
) AS t1 ON du.uId = t1.userCode
LEFT JOIN agency_level AS al ON al.alId = t1.alId
WHERE du.dcId = ? AND du.duParentUid = ?
GROUP BY al.alName;";

		$list = DB::select($sql, [$sId, $sId, $sId, $userCode]);
		
		return $list;
	}
	
	// 获取下下级身份人数
	public function getUnderTheLowerLevelCount($sId, $userCode){
		
		$sql = "SELECT du.uId, t1.*, al.alName, COUNT(1) AS num
FROM distribution_users AS du
LEFT JOIN 
(
SELECT mu.muNickName AS userName, mu.muCode AS userCode, mu.alId, mu.mcId AS sId
FROM member_users AS mu
WHERE mu.mcId = ?
UNION
SELECT au.name AS userName1, au.auCode AS userCode1,au.alId, au.sId
FROM admin_users AS au
WHERE au.sId = ?
) AS t1 ON du.uId = t1.userCode
LEFT JOIN agency_level AS al ON al.alId = t1.alId
WHERE du.dcId = ? AND du.duGrandUid = ?
GROUP BY al.alName;";

		$list = DB::select($sql, [$sId, $sId, $sId, $userCode]);
		
		return $list;
	}
	
	public function getLowerTree($sId, $userCode){
		
		$lowerList = $this->getLowerList($sId, $userCode);
		$underTheLowerList = $this->getUnderTheLowerList($sId, $userCode);

		$treeListInfo = array();

		foreach($lowerList as $lowerInfo){
			array_push($treeListInfo, [$lowerInfo, 1]);
			foreach($underTheLowerList as $underTheLowerInfo){
				if ($lowerInfo->uId == $underTheLowerInfo->duParentUid){
					array_push($treeListInfo, [$underTheLowerInfo, 2]);
					break;
				}
			}
		}
		
		return $treeListInfo;
	}
	
	// 获取分销总值
	public function getDisList($sId, $userCode, $beginDay, $endDay){
		
		$sql = "SELECT
	*
FROM
	distribution_bill AS db
WHERE
	db.dcId = ?
AND (
	(
		db.uId = ?
		AND db.dbSaleMoney > 0
	)
	OR (
		db.duParentUid = ?
		AND db.dbParentMoney > 0
	)
	OR (
		db.duGrandUid = ?
		AND db.dbGrandMoney > 0
	)
)
AND createTime >= ?
AND createTime <= ?";

		$list = DB::select($sql, [$sId, $userCode, $userCode, $userCode, $beginDay, $endDay]);

		return $list;
	}
	
	public function getDisListForCount($sId, $userCode, $beginDay, $endDay){
		
		$sql = "SELECT
	*, uId AS userCode, dbSaleMoney AS getMoney , 1 AS disLevel
FROM
	distribution_bill AS db
WHERE
	db.dcId = ?
AND db.uId = ?
AND db.dbSaleMoney > 0
AND createTime >= ?
AND createTime <= ?
UNION
SELECT
	*, duParentUid AS userCode, dbParentMoney AS getMoney , 2 AS disLevel
FROM
	distribution_bill AS db
WHERE
	db.dcId = ?
AND db.duParentUid = ?
AND db.dbParentMoney > 0
AND createTime >= ?
AND createTime <= ?
UNION
SELECT
	*, duGrandUid AS userCode, dbGrandMoney AS getMoney , 2 AS disLevel
FROM
	distribution_bill AS db
WHERE
	db.dcId = ?
AND db.duGrandUid = ?
AND db.dbGrandMoney > 0
AND createTime >= ?
AND createTime <= ?";

		$list = DB::select($sql, [$sId, $userCode, $beginDay, $endDay, 
								  $sId, $userCode, $beginDay, $endDay, 
								  $sId, $userCode, $beginDay, $endDay]);

		return $list;
	}
	
	// 获取每日分销统计
	public function getDisCount($sId, $userCode, $beginDay, $endDay){
		
		$sql = "SELECT
	sd.`day`,
	IFNULL(t1.getDayMoney, 0) AS getDayMoney
FROM
	sys_day AS sd
LEFT JOIN (
	SELECT
		*, SUM(getMoney) AS getDayMoney,
		date_format(t.createTime, '%Y-%m-%d') AS countDay
	FROM
		(
			SELECT
				*, uId AS userCode,
				dbSaleMoney AS getMoney,
				1 AS disLevel
			FROM
				distribution_bill AS db
			WHERE
				db.dcId = ?
			AND db.uId = ?
			AND db.dbSaleMoney > 0
			AND createTime >= ?
			AND createTime <= ?
			UNION
				SELECT
					*, duParentUid AS userCode,
					dbParentMoney AS getMoney,
					2 AS disLevel
				FROM
					distribution_bill AS db
				WHERE
					db.dcId = ?
				AND db.duParentUid = ?
				AND db.dbParentMoney > 0
				AND createTime >= ?
				AND createTime <= ?
				UNION
					SELECT
						*, duGrandUid AS userCode,
						dbGrandMoney AS getMoney,
						2 AS disLevel
					FROM
						distribution_bill AS db
					WHERE
						db.dcId = ?
					AND db.duGrandUid = ?
					AND db.dbGrandMoney > 0
					AND createTime >= ?
					AND createTime <= ?
		) AS t
	GROUP BY
		date_format(t.createTime, '%Y-%m-%d')
) AS t1 ON sd.`day` = t1.countDay
WHERE
	sd.`day` >= ?
AND sd.`day` <= ?
ORDER BY
	sd.`day`";
	
		$list = DB::select($sql, [$sId, $userCode, $beginDay, $endDay, 
									$sId, $userCode, $beginDay, $endDay, 
									$sId, $userCode, $beginDay, $endDay, 
									$beginDay, $endDay]);
		
		return $list;
	}
	
	// 获取年月分销统计
	public function getDisMonthCount($sId, $userCode, $beginMonth, $endMonth){
		
		$sql = "SELECT
	sm.`month`,
	IFNULL(t1.getMonthMoney, 0) AS getMonthMoney
FROM
	sys_month AS sm
LEFT JOIN (
	SELECT
		*, SUM(getMoney) AS getMonthMoney,
		date_format(t.createTime, '%Y-%m') AS countMonth
	FROM
		(
			SELECT
				*, uId AS userCode,
				dbSaleMoney AS getMoney,
				1 AS disLevel
			FROM
				distribution_bill AS db
			WHERE
				db.dcId = ?
			AND db.uId = ?
			AND db.dbSaleMoney > 0
			AND createTime >= ?
			AND createTime < ?
			UNION
				SELECT
					*, duParentUid AS userCode,
					dbParentMoney AS getMoney,
					2 AS disLevel
				FROM
					distribution_bill AS db
				WHERE
					db.dcId = ?
				AND db.duParentUid = ?
				AND db.dbParentMoney > 0
				AND createTime >= ?
				AND createTime < ?
				UNION
					SELECT
						*, duGrandUid AS userCode,
						dbGrandMoney AS getMoney,
						2 AS disLevel
					FROM
						distribution_bill AS db
					WHERE
						db.dcId = ?
					AND db.duGrandUid = ?
					AND db.dbGrandMoney > 0
					AND createTime >= ?
					AND createTime < ?
		) AS t
	GROUP BY
		date_format(t.createTime, '%Y-%m')
) AS t1 ON sm.`month` = t1.countMonth
WHERE
	sm.`month` >= ?
AND sm.`month` < ?
ORDER BY
	sm.`month`";
	
		$list = DB::select($sql, [$sId, $userCode, $beginMonth, $endMonth, 
									$sId, $userCode, $beginMonth, $endMonth, 
									$sId, $userCode, $beginMonth, $endMonth, 
									$beginMonth, $endMonth]);
		
		return $list;
	}

	// 获取分销记录
	public function getOrderDis($sId, $orderId){

		$disSql = "SELECT
	*
FROM
	distribution_bill AS db
LEFT JOIN (
	SELECT
		tb0.*, al.alName AS alName0
	FROM
		(
			SELECT
				mu.muNickName AS userName0,
				mu.muCode AS userCode0,
				mu.alId AS alId0
			FROM
				member_users AS mu
			WHERE
				mu.mcId = ?
			UNION
				SELECT
					au.name AS userName0,
					au.auCode AS userCode0,
					au.alId AS alId0
				FROM
					admin_users AS au
				WHERE
					au.sId = ?
		) AS tb0
	LEFT JOIN agency_level AS al ON tb0.alId0 = al.alId
) AS t0 ON db.dbCustomerUid = t0.userCode0
LEFT JOIN (
	SELECT
		tb1.*, al.alName AS alName1
	FROM
		(
			SELECT
				mu.muNickName AS userName1,
				mu.muCode AS userCode1,
				mu.alId AS alId1
			FROM
				member_users AS mu
			WHERE
				mu.mcId = ?
			UNION
				SELECT
					au. NAME AS userName1,
					au.auCode AS userCode1,
					au.alId AS alId1
				FROM
					admin_users AS au
				WHERE
					au.sId = ?
		) AS tb1
	LEFT JOIN agency_level AS al ON tb1.alId1 = al.alId
) AS t1 ON db.uId = t1.userCode1
LEFT JOIN (
	SELECT
		tb2.*, al.alName AS alName2
	FROM
		(
			SELECT
				mu.muNickName AS userName2,
				mu.muCode AS userCode2,
				mu.alId AS alId2
			FROM
				member_users AS mu
			WHERE
				mu.mcId = ?
			UNION
				SELECT
					au. NAME AS userName2,
					au.auCode AS userCode2,
					au.alId AS alId2
				FROM
					admin_users AS au
				WHERE
					au.sId = ?
		) AS tb2
	LEFT JOIN agency_level AS al ON tb2.alId2 = al.alId
) AS t2 ON db.duParentUid = t2.userCode2
LEFT JOIN (
	SELECT
		tb3.*, al.alName AS alName3
	FROM
		(
			SELECT
				mu.muNickName AS userName3,
				mu.muCode AS userCode3,
				mu.alId AS alId3
			FROM
				member_users AS mu
			WHERE
				mu.mcId = ?
			UNION
				SELECT
					au. NAME AS userName3,
					au.auCode AS userCode3,
					au.alId AS alId3
				FROM
					admin_users AS au
				WHERE
					au.sId = ?
		) AS tb3
	LEFT JOIN agency_level AS al ON tb3.alId3 = al.alId
) AS t3 ON db.duGrandUid = t3.userCode3
WHERE
	db.dcId = ? AND db.orderId = ? 
ORDER BY
	dbId DESC";

		$disInfo = DB::select($disSql, [$sId, $sId, $sId, $sId, $sId, $sId, $sId, $sId, $sId, $orderId]);
		
		if (empty($disInfo)){
			return false;
		}
		
		return $disInfo[0];
	}
}

?>